package com.g2room.driving.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

public class DbUtil {
	static Connection connection = null;
//	static Connection qgshConnection = null;
	public static final String ConnType_KCJQ = "kcjq";
	public static final String ConnType_QGSH = "qgsh";
	public static Connection getQgshConn() {
		return getConn();
	}
	
	public static Connection getConn() {
		if(null == connection) {
			String url = "";
			String username = "";
			String password = "";
			Properties prop = new Properties();
			File file = new File(Constants.propFilePath);
			FileInputStream fis = null;
			try {
				fis = new FileInputStream(file);
				prop.load(fis);
				url = prop.getProperty("url");
				username = prop.getProperty("username");
				password = prop.getProperty("password");
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				try {
					if(null != fis) fis.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			try {
					connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
					"jdbc:mysql://"+url+"/r4g9sj29e2?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true", 
					username, password);
//					connection = new DbUtil().getConn("com.mysql.jdbc.Driver", 
//							"jdbc:mysql://rm-bp1y3tih0vawe18v1.mysql.rds.aliyuncs.com/r4g9sj29e2?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true", 
//							"publish", "123456Gxg");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return connection;
	}

	/**
	 * 获取数据库连接对象，这里只获得，需要自己在使用后进行关闭。
	 *
	 * @return 数据库连接实例
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @auther <a href="mailto:g2room@163.com">天涯</a>
	 * Jul 6, 2013 8:49:25 PM
	 */
	public Connection getConn(String driverName, String url, String username, String pwd)
			throws ClassNotFoundException, SQLException {
		// 1.加载驱动
		Class.forName(driverName);
		// 2.得到连接
		return DriverManager.getConnection(url, username, pwd);
	}
	public static void closeConn() {
		closeConn(connection);
	}
	public static void closeConn(Connection conn) {
		if(null != conn) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		conn = null;
	}
	
	public static String decodeSpecialCharsWhenLikeUseBackslash(String content) {
        // 单引号是oracle字符串的边界,oralce中用2个单引号代表1个单引号
        String afterDecode = content.replaceAll("'", "''");
        // 由于使用了/作为ESCAPE的转义特殊字符,所以需要对该字符进行转义
        // 这里的作用是将"a/a"转成"a//a"
        afterDecode = afterDecode.replaceAll("/", "//");
        // 使用转义字符 /,对oracle特殊字符% 进行转义,只作为普通查询字符，不是模糊匹配
        afterDecode = afterDecode.replaceAll("%", "/%");
        // 使用转义字符 /,对oracle特殊字符_ 进行转义,只作为普通查询字符，不是模糊匹配
        afterDecode = afterDecode.replaceAll("_", "/_");
        return afterDecode;
    }
	

	public static Date getPublishTime(int id) {
		Date date = null;
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "select publish_time from d_update where id=" + id;
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				date = rs.getTimestamp("publish_time");
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return date;
	}

	public static String getPublishTimeStr(int id) {
		String publishTime="";
		try {
			Date date = getPublishTime(id);
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			publishTime = format.format(date);
		} catch(Exception e) {
			e.printStackTrace();
		}
		return publishTime;
	}
	
	public static void updateTime(int id) {
		try {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String publishTime = format.format(new Date());
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "update d_update set publish_time = '"+publishTime+"' where id=" + id;
			statement.execute(sql);
			statement.close();
			
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static boolean titleExist(String title, String connType) {
		boolean rsb = false;
		try {
			Connection connection = null;
			switch(connType) {
				case "kcjq" :
					connection = DbUtil.getConn();
					break;
				case "qgsh" :
					connection = DbUtil.getQgshConn();
					break;
			}
			Statement statement = connection.createStatement();
			String sql = "select title from d_article_grap where title='" + title + "'";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				rsb = true;
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return rsb;
	}
}
